package com.liholin.springboot.sample.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.STRING;

/**
 * 分析excel表格内容返回
 */
public class AnalysisExcelData {

    public synchronized static List<List<String>> getExcelData(MultipartFile file) {
        List<List<String>> list = new ArrayList<>();
        try {
            checkFile(file);
            //获得Workbook工作薄对象
            Workbook workbook = getWorkBook(file);
            //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
            if (workbook != null) {
                for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                    //获得当前sheet工作表
                    Sheet sheet = workbook.getSheetAt(sheetNum);
                    if (sheet == null) {
                        continue;
                    }
                    //获得当前sheet的开始行
                    int firstRowNum = sheet.getFirstRowNum();
                    //获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();
                    //循环除了所有行,如果要循环除第一行以外的就firstRowNum+1
                    for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                        //获得当前行
                        Row row = sheet.getRow(rowNum);
                        if (row == null) {
                            continue;
                        }
                        //获得当前行的开始列
                        int firstCellNum = row.getFirstCellNum();
                        //获得当前行的列数
                        int lastCellNum = row.getLastCellNum();
                        if (lastCellNum > 0) {
                            ArrayList<String> cellValues = new ArrayList<>();
                            //循环当前行
                            for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                                Cell cell = row.getCell(cellNum);
                                if (cell == null) {
                                    continue;
                                }
                                cellValues.add(getCellValue(cell));
                            }
                            list.add(cellValues);
                        }
                    }
                }
            }
        } catch (Exception e) {
//            throw new ServiceException(ErrorCode.ANALYSIS_EXCEL_ERROR);
        }
        return list;
    }

    /**
     * 检查文件
     *
     * @param file
     * @throws IOException
     */
    private static void checkFile(MultipartFile file) {
        //判断文件是否存在
        if (null == file) {//todo
//           throw new ServiceException(ErrorCode.NO_FOUND_RECORD_ERROR);
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {//todo
//            throw new ServiceException(ErrorCode.FORMAT_NOT_FILE);
        }
    }

    private static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007 及2007以上
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {//todo
//            throw new ServiceException(ErrorCode.ANALYSIS_EXCEL_ERROR);
        }
        return workbook;
    }

    private static String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case FORMULA:
                return cell.getCellFormula();
            case NUMERIC:
                cell.setCellType(STRING);
                return cell.getStringCellValue();
            case STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }
}
